import xlwings as xw
from utils.handle_path import PollyPath2

class ExcelHandle:
    def __init__(self,excel_filename,sheetorder=0):
        self.excel_filename = excel_filename
        self.sheetorder = sheetorder
        self.wb = xw.Book(PollyPath2.xlsmfile_path + excel_filename)
        self.sheet = self.wb.sheets[sheetorder]

    def get_excel_area_data(self,columns= None):
        B2_B7_value = self.sheet.range(columns).options(ndim=2).value
        # if len(B2_B7_value) == 5:
        #     B2_B7_value.append([''])
        excel_in_dic =  \
        {
            "Account":B2_B7_value[0][0],
            "Password":B2_B7_value[1][0],
            "Directory":B2_B7_value[2][0],
            "Csdshunxu":B2_B7_value[3][0],
            "BugtitleAppend":int(B2_B7_value[4][0]),
            "Curappoint":B2_B7_value[5][0],
        }
        return excel_in_dic

    def get_excel_case_data(self):
        '''
        获取excel中J2的值
        1.如果为None（excel中为空），则动态取D2到H列最后一行范围的值；
        2.如果长度大于4，则取J2的值范围的值；
        3.如果长度为2到4，则取J2的值范围的值；
        :return: data1,rangexlsm,rangexlsmafter
        '''
        bugrange = self.sheet.range('J2').value
        if bugrange == None:
            data1 = self.sheet.range('d2').options(expand='table').value
            rangexlsm = 1
            rangexlsmafter = ''
        elif len(bugrange) > 4 :
            data1 = self.sheet.range(bugrange).value
            rangexlsm = int(bugrange.split(":")[0][1:])-1
            rangexlsmafter = ':'+bugrange.split(":")[1]
        elif 2<= len(bugrange) <= 4:
            data1 = self.sheet.range(bugrange).options(expand='table').value
            rangexlsm = int(bugrange[1:])-1
            rangexlsmafter = ''
        return data1,rangexlsm,rangexlsmafter

    def wirte_range(self,dyrange):
        '''
        写入取值范围
        :param dyrange:
        :return: NONE
        '''
        # dyrange = ['D'+str(i+rangexlsm+2)+rangexlsmafter]
        self.sheet.range('j2').value = dyrange
        self.wb.save()

if __name__ == '__main__':
    # from utils.handle_excel_xlwings import ExcelHandle
    # dic = ExcelHandle('casetobug.xlsm').get_excel_area_data('B2:B7')
    # data1,rangexlsm,rangexlsmafter= ExcelHandle('casetobug.xlsm').get_excel_case_data()
    excel_in_dic = ExcelHandle('casetobug.xlsm').get_excel_area_data('B2:B7')
    print(excel_in_dic)
    print(excel_in_dic['Curappoint'])
    print(type(excel_in_dic))